Permissions on system tables should be restricted to authorized accounts.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-2458	DM1749-SQLServer9	SV-23790r2_rule	ECLP-1	Medium

Description
Microsoft SQL Server defaults to allow all users to view the majority of the system tables. The system tables contain information such as login IDs, permissions, objects and even the text of all stored procedures. In a secure environment, any direct access granted to these tables by users bypasses security controls defined within the associated system procedures and views. The bypass of these controls can lead to unauthorized viewing of sensitive data.

Description

Microsoft SQL Server defaults to allow all users to view the majority of the system tables. The system tables contain information such as login IDs, permissions, objects and even the text of all stored procedures. In a secure environment, any direct access granted to these tables by users bypasses security controls defined within the associated system procedures and views. The bypass of these controls can lead to unauthorized viewing of sensitive data.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-22796r2_chk )
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], t.name AS [Table], p.permission_name AS [Permission], p.state_desc AS [State] FROM sys.database_principals u JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id JOIN sys.tables t ON p.major_id = t.object_id WHERE p.state_desc <> 'DENY' AND t.is_ms_shipped = 1 ORDER BY u.name, t.name Review the list of permissions assigned to system tables. If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding. Note: By default, SELECT permission is granted to the PUBLIC role for system tables in all databases. Even though permission is set by default, it is still a Finding.

Check Text ( C-22796r2_chk )

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal], t.name AS [Table], p.permission_name AS [Permission], p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.tables t ON p.major_id = t.object_id
WHERE p.state_desc <> 'DENY'
AND t.is_ms_shipped = 1
ORDER BY u.name, t.name

Review the list of permissions assigned to system tables.

If any results listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.

Note: By default, SELECT permission is granted to the PUBLIC role for system tables in all databases. Even though permission is set by default, it is still a Finding.

Fix Text (F-24604r1_fix)
Revoke permissions granted to system tables where supported by the DBMS vendor. From the query prompt: USE [database name] REVOKE [permission] ON [object name] FROM [user name] Document permission grants in the System Security Plan and authorize with the IAO.